#include "model.h"

//创建数据库
sqlite3 *db_create()
{
    sqlite3 *db;
    if (sqlite3_open(DBFILE, &db) != SQLITE_OK)
    {
        printf("error: %s\n", sqlite3_errmsg(db));
    }
    return db;
}

//关闭数据库
int db_close(sqlite3 *db)
{
    sqlite3_close(db);
    return 0;
}
//创建数据库表
int db_staff_create(sqlite3 *db)
{
    char mysql[1024] = "CREATE TABLE if not exists staff(id integer primary key,user_type integer,name text,passwd text,age integer,phone text,addr text,position text,join_at text,level integer,salary REAL)";
    char *errmsg;
    int ret = sqlite3_exec(db, mysql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("staff表创建成功\n");
    }

    return 0;
}

int db_history_create(sqlite3 *db)
{
    char mysql[1024] = "create table if not exists history(id INTEGER primary key AUTOINCREMENT, time text,name text,words text)";
    char *errmsg;
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("history创建成功\n");
    }
    return 0;
}

//添加用户
int db_insert(sqlite3 *db, staff_info_t staff)
{
    char insert_sql[1024] = {0};
    char *errmsg;
    sprintf(insert_sql, "insert into staff values(%d,%d,'%s','%s',%d,'%s','%s','%s','%s',%d,%f)", staff.no, staff.user_type, staff.name, staff.passwd, staff.age, staff.phone, staff.addr, staff.position, staff.join_at, staff.level, staff.salary);
    if (sqlite3_exec(db, insert_sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
        return -1;
    }

    return 0;
}

//查找所有
int db_check_all(sqlite3 *db)
{
    char mysql[1024] = "select * from staff";
    char **ret;
    int nrow, ncolum;
    char *errmsg;
    if (sqlite3_get_table(db, mysql, &ret, &nrow, &ncolum, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
        return -1;
    }
    return 0;
}

//员工查找自己的记录
staff_info_t db_check_name(sqlite3 *db, char *sql)
{
    if (sql == NULL)
    {
        printf("查找的语句格式不正确，请重新输入\n");
        exit(0);
    }

    char *errmsg;
    char **ret;
    int nrow, ncolum;

    if (sqlite3_get_table(db, sql, &ret, &nrow, &ncolum, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
    }
    int i, n = ncolum;
    staff_info_t staff;
    for (i = 0; i < (nrow + 1) * ncolum; i++)
    {
        if (n <= ncolum + nrow * ncolum - 1)
        {
            switch (n % 11)
            {
            case 0:
                staff.no = atoi(ret[n]);
                break;
            // user_type
            case 1:
                staff.user_type = atoi(ret[n]);
                break;
                // name
            case 2:
                printf("name === %s\n", ret[n]);
                strcpy(staff.name, ret[n]);
                break;
                // passwd
            case 3:
                strcpy(staff.passwd, ret[n]);
                break;
                // age
            case 4:
                staff.age = atoi(ret[n]);
                break;
                // phone
            case 5:
                strcpy(staff.phone, ret[n]);
                break;
                // addr
            case 6:
                strcpy(staff.addr, ret[n]);
                break;
                //position
            case 7:
                strcpy(staff.position, ret[n]);
                break;
                // join_at
            case 8:
                strcpy(staff.join_at, ret[n]);
                break;
                // level
            case 9:
                staff.level = atoi(ret[n]);
                break;
                // salary
            case 10:
                staff.salary = atof(ret[n]);
                break;
            default:
                break;
            }
        }
        n++;
    }
    return staff;
}

//查询的个数
int check_number(sqlite3 *db, char *sql)
{
    if (sql == NULL)
    {
        printf("查找的语句格式不正确，请重新输入\n");
        exit(0);
    }
    char *errmsg;
    char **ret;
    int nrow, ncolum;

    if (sqlite3_get_table(db, sql, &ret, &nrow, &ncolum, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
    }
    return nrow;
}

//修改信息
int db_change_name(sqlite3 *db, char *name, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set name = '%s' where id = %d", name, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_age(sqlite3 *db, int age, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set age = %d where id = %d", age, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_addr(sqlite3 *db, char *addr, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set addr = '%s' where id = %d", addr, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_phone(sqlite3 *db, char *phone, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set phone = '%s' where id = %d", phone, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_position(sqlite3 *db, char *positoin, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set position = '%s' where id = %d", positoin, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_salary(sqlite3 *db, double salary, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set salary = %f where id = %d", salary, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_join_at(sqlite3 *db, char *join_at, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set join_at = '%s' where id = %d", join_at, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_level(sqlite3 *db, int level, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set level = %d where id = %d", level, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}
int db_change_passwd(sqlite3 *db, char *passwd, int id)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "update staff set passwd = '%s' where id = %d", passwd, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据修改成功 ******\n");
    }
    return 0;
}

//删除用户
int db_del(sqlite3 *db, int id, char *name)
{
    char mysql[1024] = {0};
    char *errmsg;
    sprintf(mysql, "delete from staff where name = '%s' and id = %d;", name, id);
    if (sqlite3_exec(db, mysql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误：%s\n", errmsg);
        return -1;
    }
    else
    {
        printf("****** 数据删除成功 ******\n");
    }
    return 0;
}

//添加历史记录
int insert_history(sqlite3 *db, hsg_t hsg)
{
    char insert_sql[1024] = {0};
    char *errmsg;
    sprintf(insert_sql, "insert into history (time, name, words) values('%s','%s','%s')", hsg.time, hsg.name, hsg.words);
    if (sqlite3_exec(db, insert_sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
        return -1;
    }
    return 0;
}

//查询历史记录
hsg_t check_history(sqlite3 *db, char *sql)
{
    char *errmsg;
    char **ret;
    int nrow, ncolum;
    
    hsg_t hsg;

    if (sqlite3_get_table(db, sql, &ret, &nrow, &ncolum, &errmsg) != SQLITE_OK)
    {
        printf("err:%s\n", errmsg);
    }
    else
    {
        int i, n = ncolum;
        for (i = 0; i < (nrow + 1) * ncolum; i++)
        {
            if (n <= ncolum + nrow * ncolum - 1)
            {
                switch (n % 4)
                {
                case 1:
                    strcpy(hsg.time, ret[n]);
                    break;
                case 2:
                    strcpy(hsg.name, ret[n]);
                    break;
                case 3:
                    strcpy(hsg.words, ret[n]);
                    break;
                default:
                    break;
                }
            }
            n++;
        }
        printf("查询成功!\n");
    }

    printf("hsg: %s: %s: %s\n", hsg.time, hsg.name, hsg.words);

    return hsg;
}


#if 0
//调试
int main(int argc, char const *argv[])
{
    sqlite3 *db;
    char *sql = "select * from staff where 1 limit 1";
    char *sql1 = "select * from history where 1 limit 1";
    db = db_create();
    db_staff_create(db);
    db_history_create(db);
    staff_info_t staff_db = {
        1001,
        2,
        "namexxxx",
        "passwdxxxx",
        45,
        "phonexxxx",
        "addrxxxx",
        "positionxxxx",
        1,
        "join_atxxxxx",
        4.5,
    };


    hsg_t hsg_db = {
        "2020-02-02",
        "adc",
        "nihao",
    };


    db_insert(db, staff_db);


    staff_info_t staff;

    int age = 10;
    char * addr = "**addr";
    char * phone = "**phone";
    char * positoin = "**position";
    double salary = 2.5;
    char *join_at  = "**join_at";
    int level = 2;
    char * passwd = "**passwd";

    db_change_age(db,age,1001);
    db_change_addr(db,addr,1001);
    db_change_phone(db,phone,1001);
    db_change_position(db,positoin,1001);
    db_change_salary(db,salary,1001);
    db_change_join_at(db,join_at,1001);
    db_change_level(db,level,1001);
    db_change_passwd(db,passwd,1001);






    return 0;
}
#endif
